How to retrieve data from an external database in a CheckBoxList control and consuming it inside the form
Last Update: July 10, 2017
Here are the steps on how to do that:
- Open the form in
design mode.
- Create a form that looks like the below image where SQLGetEmployeeInfo2 is a SQL Connector control:
- Select the SQLGetEmployeeInfo2 then select the
Control Settings property in the
Control Properties pane. A Database Dialog Settings will appear. Set the
Initial Connection Settings as follows:
Click Connect, then set the
Database Content settings as follows (herein, we will connect to "Employees" table stored in MS SQL Server DB):
Please note that you can include the following:
- A Where statement to filter the SQL query. For example, you can specify one of the following statements in the Where:
- Where clause that based on static value. Example: Business_Unit_Name = ‘IT’.
- Where clause that based on a dynamic value of another control in the form.
Example: Business_Unit_Name = ‘#Control(TextBox9)’. - Where clause that include composite values. Example: Business_Unit_Name = ‘#Control(TextBox9)’ AND Business_Unit_Name LIKE ‘%Admin%’ AND Position LIKE ‘%Manager%’
- When click on OK, the system saves the Database Dialog Settings and create a new system rule to execute the executeSQLQuery. Don’t delete this rule.
- Check the option "Show All Data When Search Box is Empty". If checked, the data will be retrieved automatically when open the search dialog in the run-mode without the need to specify the value in the text-search criteria.
- Ignore SQL Error: To ignore any SQL error that may fire at runtime.
- Select SQLGetEmployeeInfo2 control.
- In the
Rules pane, click on the Add icon to add a new rule, the
Rule Manager dialog will open.
- Add a new rule as follows:
A. Change the
Rule Name as desired.
B. Change the
Rule Type to Action.
C. Change the
Event Type to onChange. Check "Execute if event triggered only on this control".
D. In the
Action section, type the following script:
generateSQLData(SQLGetEmployeeInfo2, ['Location_Name', 'Business_Unit_Name', 'Position'], ['txtLocation', 'txtDepartment', 'txtPosition'])
E. Click on the
Save button to save the rule.
Note: You can use
Assistance Panel to help you adding functions and related parameters, form variables and form controls to
Conditions and/or
Actions sections. - Test the form by clicking on the
Preview button in the
View group.
Related Resources:
These resources may not reflect the same exact case steps.
https://www.youtube.com/embed/x_Ch55KnYOk